<?php

namespace App\Repositories;

use App\Http\Controllers\Controller;
use App\Models\AcademicPeriod;
use App\Models\ConfigItem;
use App\Models\ContactType;
use App\Models\StaffCustomFormField;
use App\Models\FieldOption;
use App\Models\UserIdentities;
use App\Models\SecurityUsers;
use App\Models\Institutions;
use App\Models\UserSpecialNeedsAssessment;
use App\Models\InstitutionStudent;
use App\Models\InstitutionStudentTransfers;
use App\Models\InstitutionStudentWithdraw;
use App\Models\StudentCustomFieldValues;
use App\Models\InstitutionStaff;
use App\Models\StaffCustomFieldValues;
use App\Models\GuardianRelation;
use App\Models\InstitutionPositions;
use App\Models\StaffTypes;
use App\Models\StaffPositionTitleGrade;
use App\Models\StaffPositionGrades;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use JWTAuth;
use Illuminate\Support\Facades\DB;

class DirectoryRepository extends Controller
{

    //For POCOR-8104 Start...
    public function getUserTypeList($params)
    {
        try {
            $userTypes = config('constantvalues.userTypes');
            $list = [];
            $c = 0;
            foreach ($userTypes as $k => $uT) {
                $list[$c]['id'] = $k;
                $list[$c]['name'] = $uT;

                $c++;
            }
            
            return $list;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch User Type List from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('User Type List Not Found');
        }
    }


    public function getUniqueOpenemisId($params)
    {
        try {
            $openEmisNo = getNewOpenemisNo();

            $resp['openemis_no'] = $openEmisNo;
            return $resp;
        } catch (\Exception $e) {
            Log::error(
                'Failed to generate Openemis Id.',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Failed to generate Openemis Id.');
        }
    }


    public function getAutoGeneratedPassword($params)
    {
        try {
            $resp = [];
            $configItemTable = new ConfigItem();

            $configItem = $configItemTable->where('code', '=', 'password_min_length')->first();

            $passwordLength = intval($configItem->value) > 3 ? intval($configItem->value) : 4;

            $numberOfGroup = 4;
            $sumTo = $passwordLength;

            $groups = [];

            while (count($groups) != 4) {
                $group = 0;
                $groups = [];
                while (array_sum($groups) != $sumTo) {
                    $groups[$group] = mt_rand(1, $sumTo / mt_rand(1, $numberOfGroup));
                    if (++$group == $numberOfGroup) {
                        $group = 0;
                    }
                }
            }

            $upperCaseData = $configItemTable->where('code', '=', 'password_has_uppercase')->first();

            $upperCase = intval($upperCaseData->value) ? $groups[1] : 0;

            $numericalData = $configItemTable->where('code', '=', 'password_has_number')->first();
            $numerical = intval($numericalData->value) ? $groups[2] : 0;


            $specialCharacter = $configItemTable->where('code', '=', 'password_has_non_alpha')->first();
            $specialCharacter = intval($specialCharacter->value) ? $groups[3] : 0;

            //$password = generatePassword($passwordLength, $upperCase, $numerical, $specialCharacter);

            $l = $passwordLength;
            $c = $upperCase;
            $n = $numerical;
            $s = $specialCharacter;

            $out = '';
            // get count of all required minimum special chars
            $count = $c + $n + $s;


            // sanitize inputs; should be self-explanatory
            if (!is_int($l) || !is_int($c) || !is_int($n) || !is_int($s)) {
                return 1;
            } elseif ($l < 0 || $c < 0 || $n < 0 || $s < 0) {
                return 2;
            } elseif ($c > $l) {
                return 3;
            } elseif ($n > $l) {
                return 4;
            } elseif ($s > $l) {
                return 5;
            } elseif ($count > $l) {
                return 6;
            }


            // change these strings if you want to include or exclude possible password characters
            $chars = "abcdefghijklmnopqrstuvwxyz";
            $caps = strtoupper($chars);
            $nums = "0123456789";
            $syms = "!@#$%^&*()-+?";


            // build the base password of all lower-case letters
            for ($i = 0; $i < $l; $i++) {
                $out .= substr($chars, mt_rand(0, strlen($chars) - 1), 1);
            }

            // create arrays if special character(s) required
            if ($count) {
                // split base password to array; create special chars array
                $tmp1 = str_split($out);
                $tmp2 = array();

                // Do not change implementation to using mt_rand to rand unless in PHP 7 as rand will have predicable pattern
                // add required special character(s) to second array
                for ($i = 0; $i < $c; $i++) {
                    array_push($tmp2, substr($caps, mt_rand(0, strlen($caps) - 1), 1));
                }
                for ($i = 0; $i < $n; $i++) {
                    array_push($tmp2, substr($nums, mt_rand(0, strlen($nums) - 1), 1));
                }
                for ($i = 0; $i < $s; $i++) {
                    array_push($tmp2, substr($syms, mt_rand(0, strlen($syms) - 1), 1));
                }
                // hack off a chunk of the base password array that's as big as the special chars array
                $tmp1 = array_slice($tmp1, 0, $l - $count);
                // merge special character(s) array with base password array
                $tmp1 = array_merge($tmp1, $tmp2);
                // mix the characters up
                shuffle($tmp1);
                // convert to string for output
                $out = implode('', $tmp1);
            }

            $resp['password'] = $out;

            return $resp;
            
        } catch (\Exception $e) {
            Log::error(
                'Failed to generate Password.',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Failed to generate Password.');
        }
    }



    public function getContactTypes($params)
    {
        try {
            $contactTypes = ContactType::select('id', 'name')->get();

            $list = [];
            foreach ($contactTypes as $key => $contactType) {
                $list[$key]['id'] = $contactType['id'];
                $list[$key]['name'] = $contactType['name'];
            }
            return $list;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Contact Type List from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Contact Type List Not Found');
        }
    }



    public function getStaffCustomFields($params)
    {
        try {
            $customFields = StaffCustomFormField::with([
                    'staffCustomField',
                    'staffCustomField.staffCustomFieldOption:id as option_id,name as option_name,is_default,visible,order as option_order,staff_custom_field_id'
                ])
                ->whereHas('staffCustomField')
                ->where('staff_custom_form_id', 1)
                ->orderBy('order', 'ASC')
                ->get()
                ->toArray();

            return $customFields;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Staff Custom Fields List from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Staff Custom Fields List Not Found');
        }
    }


    public function getFieldOptions($params)
    {
        try {
            $list = FieldOption::get()->toArray();

            $total = count($list);

            $resp['list'] = $list;
            $resp['total'] = $total;
            
            return $resp;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Field Options List from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Field Options List Not Found');
        }
    }


    public function getFieldOptionData($params, $fieldOptionId)
    {
        try {
            $data = FieldOption::where('id', $fieldOptionId)->first();
            $resp = [];

            if($data){
                $resp['data'] = $data;
            }
            

            return $resp;
            
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Field Option Data from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Field Option Data Not Found');
        }
    }



    public function getUserByIdentityNumber($params, $identityTypeId, $identityNumber)
    {
        try {
            $resp = [];
            $user_id = $params['user_id'] ?? null;
            $nationality_id = $params['nationality_id'] ?? null;

            $checkUser = UserIdentities::with('user:id,first_name,middle_name,third_name,last_name,openemis_no,preferred_name')->where('identity_type_id', $identityTypeId)->where('number', 'LIKE', '%'.$identityNumber.'%');

            if($user_id){
                $checkUser = $checkUser->where('security_user_id', $user_id);
            }

            if($nationality_id){
                $checkUser = $checkUser->where('nationality_id', $nationality_id);
            }

            $checkUser = $checkUser->get();
            
            if($checkUser){
                $checkUser = $checkUser->toArray();
                $resp['user_exist'] = 1;
                $resp['data'] = $checkUser;
            } else {
                $resp['user_exist'] = 0;
                $resp['data'] = $checkUser;
            }
            return $resp;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch User Data from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('User Data Not Found');
        }
    }


    public function getUserByBasicInfo($requestDataParams)
    {
        try {
            $institutionId = (array_key_exists('institution_id', $requestDataParams)) ? $requestDataParams['institution_id'] : null;
            $userTypeId = (array_key_exists('user_type_id', $requestDataParams)) ? $requestDataParams['user_type_id'] : null;
            $firstName = (array_key_exists('first_name', $requestDataParams)) ? $requestDataParams['first_name'] : null;
            $lastName = (array_key_exists('last_name', $requestDataParams)) ? $requestDataParams['last_name'] : null;
            $openemisNo = (array_key_exists('openemis_no', $requestDataParams)) ? $requestDataParams['openemis_no'] : null;
            $identityNumber = (array_key_exists('identity_number', $requestDataParams)) ? $requestDataParams['identity_number'] : null;
            $dateOfBirth = (array_key_exists('date_of_birth', $requestDataParams)) ? $requestDataParams['date_of_birth'] : null;
            $identityTypeId = (array_key_exists('identity_type_id', $requestDataParams)) ? $requestDataParams['identity_type_id'] : null;
            $nationalityId = (array_key_exists('nationality_id', $requestDataParams)) ? $requestDataParams['nationality_id'] : null;
            $limit = (array_key_exists('limit', $requestDataParams)) ? $requestDataParams['limit'] : 10;
            $page = (array_key_exists('page', $requestDataParams)) ? $requestDataParams['page'] : 1;
            $get_user_id = (array_key_exists('id', $requestDataParams)) ? $requestDataParams['id'] : null;


            $identityNumber == '';


            $whereConditions = [];

            $totalCount = 0;
            if ($identityNumber == '') {
                $security_users_result = SecurityUsers::leftjoin('genders', 'genders.id', '=', 'security_users.gender_id')
                    ->leftJoin('identity_types', 'identity_types.id', '=', 'security_users.identity_type_id')
                    ->leftJoin('user_identities', 'user_identities.security_user_id', '=', 'security_users.id')
                    ->leftJoin('nationalities', 'nationalities.id', '=', 'security_users.nationality_id')
                    ->leftJoin('area_administratives', 'area_administratives.id', '=', 'security_users.address_area_id')
                    ->leftJoin('area_administratives as birthAreaAdministratives', 'birthAreaAdministratives.id', '=', 'security_users.birthplace_area_id')
                    ->where('security_users.super_admin', '!=', 1)
                    ->select('security_users.*', 
                        'genders.id as Genders_id', 
                        'genders.name as Genders_name', 
                        'identity_types.id as MainIdentityTypes_id', 
                        'identity_types.name as MainIdentityTypes_name', 
                        'nationalities.id as MainNationalities_id', 
                        'nationalities.name as MainNationalities_name', 
                        'area_administratives.name as area_name', 
                        'area_administratives.code as area_code', 
                        'birthAreaAdministratives.name as birth_area_name', 
                        'birthAreaAdministratives.code as birth_area_code', 
                        'user_identities.number as MainIdentityTypes_number', 
                    );

                if (!empty($firstName)) {
                    $security_users_result = $security_users_result->where('security_users.first_name', 'LIKE', '%'.$firstName.'%');
                }

                if (!empty($lastName)) {
                    $security_users_result = $security_users_result->where('security_users.last_name', 'LIKE', '%'.$lastName.'%');
                }

                if (!empty($openemisNo)) {
                    $security_users_result = $security_users_result->where('security_users.openemis_no', 'LIKE', '%'.$openemisNo.'%');
                }

                if (!empty($dateOfBirth)) {
                    $dateOfBirth = date_create($dateOfBirth)->format('Y-m-d');

                    $security_users_result = $security_users_result->where('security_users.date_of_birth', '=', $dateOfBirth);
                }


                if (!empty($get_user_id)) {
                    $security_users_result = $security_users_result->where('security_users.id', '=', $get_user_id);
                }

                $security_users_result = $security_users_result->groupBy('security_users.id')
                    ->get()
                    ->toArray();

                $totalCount = count($security_users_result);

            } else {
                $userTypeCondition = [];

                $get_result_by_identity_users_result = SecurityUsers::leftjoin('genders', 'genders.id', '=', 'security_users.gender_id')
                    ->leftJoin('identity_types', 'identity_types.id', '=', 'security_users.identity_type_id')
                    ->join('user_identities', function ($q) use ($identityTypeId, $identityNumber, $nationalityId){
                        $q->on('user_identities.security_user_id', '=', 'security_users.id');
                        if (!empty($identityTypeId)) {
                            $q->where('user_identities.identity_type_id', $identityTypeId);
                        }

                        if (!empty($identityNumber)) {
                            $q->where('user_identities.number', $identityNumber);
                        }

                        if (!empty($nationalityId)) {
                            $q->where('user_identities.nationality_id', $nationalityId);
                        }

                    })
                    ->leftJoin('nationalities', 'nationalities.id', '=', 'security_users.nationality_id')
                    ->leftJoin('area_administratives', 'area_administratives.id', '=', 'security_users.address_area_id')
                    ->leftJoin('area_administratives as birthAreaAdministratives', 'birthAreaAdministratives.id', '=', 'security_users.birthplace_area_id')
                    ->where('security_users.super_admin', '!=', 1)
                    ->select('security_users.*', 
                        'genders.id as Genders_id', 
                        'genders.name as Genders_name', 
                        'identity_types.id as MainIdentityTypes_id', 
                        'identity_types.name as MainIdentityTypes_name', 
                        'nationalities.id as MainNationalities_id', 
                        'nationalities.name as MainNationalities_name', 
                        'area_administratives.name as area_name', 
                        'area_administratives.code as area_code', 
                        'birthAreaAdministratives.name as birth_area_name', 
                        'birthAreaAdministratives.code as birth_area_code', 
                        'user_identities.number as MainIdentityTypes_number', 
                    )
                    ->groupBy('security_users.id')
                    ->get()
                    ->toArray();



                if (empty($get_result_by_identity_users_result)) {
                    $security_users_result = SecurityUsers::leftjoin('genders', 'genders.id', '=', 'security_users.gender_id')
                    ->leftJoin('identity_types', 'identity_types.id', '=', 'security_users.identity_type_id')
                    ->join('user_identities', function ($q) use ($identityTypeId, $identityNumber, $nationalityId){
                        $q->on('user_identities.security_user_id', '=', 'security_users.id');
                        if (!empty($identityTypeId)) {
                            $q = $q->where('user_identities.identity_type_id', $identityTypeId);
                        }

                        if (!empty($identityNumber)) {
                            $q = $q->where('user_identities.number', $identityNumber);
                        }

                        if (!empty($nationalityId)) {
                            $q = $q->where('user_identities.nationality_id', $nationalityId);
                        }

                    })
                    ->leftJoin('nationalities', 'nationalities.id', '=', 'security_users.nationality_id')
                    ->leftJoin('area_administratives', 'area_administratives.id', '=', 'security_users.address_area_id')
                    ->leftJoin('area_administratives as birthAreaAdministratives', 'birthAreaAdministratives.id', '=', 'security_users.birthplace_area_id')
                    ->where('security_users.super_admin', '!=', 1)
                    ->select('security_users.*', 
                        'genders.id as Genders_id', 
                        'genders.name as Genders_name', 
                        'identity_types.id as MainIdentityTypes_id', 
                        'identity_types.name as MainIdentityTypes_name', 
                        'nationalities.id as MainNationalities_id', 
                        'nationalities.name as MainNationalities_name', 
                        'area_administratives.name as area_name', 
                        'area_administratives.code as area_code', 
                        'birthAreaAdministratives.name as birth_area_name', 
                        'birthAreaAdministratives.code as birth_area_code', 
                        'user_identities.number as MainIdentityTypes_number', 
                    );

                    if (!empty($firstName)) {
                        $security_users_result = $security_users_result->where('security_users.first_name', 'LIKE', '%'.$firstName.'%');
                    }

                    if (!empty($lastName)) {
                        $security_users_result = $security_users_result->where('security_users.last_name', 'LIKE', '%'.$lastName.'%');
                    }

                    if (!empty($openemisNo)) {
                        $security_users_result = $security_users_result->where('security_users.openemis_no', 'LIKE', '%'.$openemisNo.'%');
                    }

                    if (!empty($dateOfBirth)) {
                        $dateOfBirth = date_create($dateOfBirth)->format('Y-m-d');

                        $security_users_result = $security_users_result->where('security_users.date_of_birth', '=', $dateOfBirth);
                    }


                    if (!empty($get_user_id)) {
                        $security_users_result = $security_users_result->where('security_users.id', '=', $get_user_id);
                    }

                    $security_users_result = $security_users_result->groupBy('security_users.id')
                    ->get()
                    ->toArray();

                } else {
                    $security_users_result = $get_result_by_identity_users_result;
                }

                $totalCount = count($security_users_result);  

            }

            $institutionsTbl = Institutions::where('id', $institutionId)->select('name as institution_name', 'code as institution_code')->first();

            $user_internal_search_result = [];
            foreach ($security_users_result AS $security_user) {
                $MainNationalities_id = !empty($security_user['MainNationalities_id']) ? $security_user['MainNationalities_id'] : '';
                $MainNationalities_name = !empty($security_user['MainNationalities_name']) ? $security_user['MainNationalities_name'] : '';
                $MainIdentityTypes_id = !empty($security_user['MainIdentityTypes_id']) ? $security_user['MainIdentityTypes_id'] : '';
                $MainIdentityTypes_name = !empty($security_user['MainIdentityTypes_name']) ? $security_user['MainIdentityTypes_name'] : '';
                $identity_number = !empty($security_user['MainIdentityTypes_number']) ? $security_user['MainIdentityTypes_number'] : '';
                $security_user_id = $security_user['id'];

                $SpecialNeedsList = UserSpecialNeedsAssessment::where('security_user_id', $security_user_id)->get();

                $SpecialNeeds = count($SpecialNeedsList);

                $has_special_needs = ($SpecialNeeds == 1) ? true : false;

                $is_same_school = $is_diff_school = $academic_period_id = $academic_period_year = 0;
                $is_pending_transfer = $is_pending_withdraw = $education_grade_id = $institution_id = 0;

                $institution_code = $institution_name =
                $pending_transfer_institution_name = $pending_transfer_institution_code =
                $pending_transfer_prev_institution_code = $pending_transfer_prev_institution_name =
                $pending_withdraw_institution_name = $pending_withdraw_institution_code = '';

                $CustomDataArray = [];

                if (!empty($userTypeId)) {
                    if ($security_user['is_student'] == 1) {
                        $account_type = 'Student';
                    } else if ($security_user['is_staff'] == 1) {
                        $account_type = 'Staff';
                    } else if ($security_user['is_guardian'] == 1) {
                        $account_type = 'Guardian';
                    } else {
                        $account_type = 'Others';
                    }


                    if ($userTypeId == 1) {

                        $student = $this->getStudent($security_user_id);

                        if (!empty($student)) {
                            $institution_id = $student->institution_id;
                            $institution_name = $student->institution_name;
                            $institution_code = $student->institution_code;
                            $academic_period_id = $student->academic_period_id;
                            $academic_period_year = $student->academic_period_year;
                            $education_grade_id = $student->education_grade_id;
                            if ($student->institution_id == $institutionId) {
                                $is_same_school = 1;
                            } else {
                                $is_diff_school = 1;
                            }
                        }

                        $pendingTransfer = $this->getPendingTransfer($security_user_id);

                        $pendingWithdraw = $this->getPendingWithdraw($security_user_id);


                        if ($pendingTransfer) {
                            $is_pending_transfer = 1;
                            $pending_transfer_institution_name = $pendingTransfer->institution_name;
                            $pending_transfer_institution_code = $pendingTransfer->institution_code;
                            $pending_transfer_prev_institution_code = $pendingTransfer->previous_institution_code;
                            $pending_transfer_prev_institution_name = $pendingTransfer->previous_institution_name;
                        }


                        if ($pendingWithdraw) {
                            $is_pending_withdraw = 1;
                            $pending_withdraw_institution_name = $pendingWithdraw->institution_name;
                            $pending_withdraw_institution_code = $pendingWithdraw->institution_code;
                        }

                        //get student custom data
                        $CustomDataArray = $this->getStudentCustomData($security_user_id);

                    }

                    if ($userTypeId == 2) {
                        $assignedStatus = 1; //For staff_status = ASSIGNED...
                        $institutionStaffTbl = InstitutionStaff::join('institutions', 'institutions.id', '=', 'institution_staff.institution_id')
                            ->where('institution_staff.staff_id', $security_user_id)
                            ->where('institution_staff.staff_status_id', $assignedStatus)
                            ->where('institution_staff.institution_id', $institutionId)
                            ->select(
                                'institution_staff.institution_id',
                                'institution_staff.staff_id',
                                'institution_staff.institution_position_id',
                                'institution_staff.staff_status_id',
                                'institutions.name as institution_name',
                                'institutions.code as institution_code',
                            )
                            ->get()
                            ->toArray();

                        if (!empty($institutionStaffTbl)) {
                            $positionArray = [];
                            $is_same_school = 1;
                            foreach ($institutionStaffTbl as $skey => $sval) {
                                $institution_id = $sval['institution_id'];
                                $institution_name = $sval['institution_name'];
                                $institution_code = $sval['institution_code'];
                                $positionArray[$skey] = $sval['institution_position_id'];
                            }
                        } else {
                            $institutionStaffTbl = InstitutionStaff::join('institutions', 'institutions.id', '=', 'institution_staff.institution_id')
                            ->where('institution_staff.staff_id', $security_user_id)
                            ->where('institution_staff.staff_status_id', $assignedStatus)
                            ->select(
                                'institution_staff.institution_id',
                                'institution_staff.staff_id',
                                'institution_staff.institution_position_id',
                                'institution_staff.staff_status_id',
                                'institutions.name as institution_name',
                                'institutions.code as institution_code',
                            )
                            ->get()
                            ->toArray();

                            if (empty($institutionStaffTbl)) {
                                $is_diff_school = 0;
                            } else {
                                $is_diff_school = 1;
                            }

                            $positionArray = [];
                            foreach ($institutionStaffTbl as $skey => $sval) {
                                $institution_id = $sval['institution_id'];
                                $institution_name = $sval['institution_name'];
                                $institution_code = $sval['institution_code'];
                                $positionArray[$skey] = $sval['institution_position_id'];
                            }

                        }

                        //get staff custom data
                        $CustomDataArray = $this->getStaffCustomData($security_user_id);

                    }

                    if ($userTypeId == 3) {
                        //$account_type = 'Guardian';
                    }
                    if ($userTypeId == 4) {
                        //$account_type = 'Others';
                    }
                }



                $user_internal_search_result[] = [
                    'id' => $security_user_id,
                    'username' => $security_user['username'],
                    'password' => $security_user['password'],
                    'openemis_no' => $security_user['openemis_no'],
                    'first_name' => $security_user['first_name'],
                    'middle_name' => $security_user['middle_name'],
                    'third_name' => $security_user['third_name'],
                    'last_name' => $security_user['last_name'],
                    'preferred_name' => $security_user['preferred_name'],
                    'email' => $security_user['email'],
                    'address' => $security_user['address'],
                    'postal_code' => $security_user['postal_code'],
                    'gender_id' => $security_user['gender_id'],
                    'external_reference' => $security_user['external_reference'],
                    'last_login' => $security_user['last_login'],
                    'photo_name' => $security_user['photo_name'],
                    'photo_content' => $security_user['photo_content'],
                    'preferred_language' => $security_user['preferred_language'],
                    'address_area_id' => $security_user['address_area_id'],
                    'birthplace_area_id' => $security_user['birthplace_area_id'],
                    'super_admin' => $security_user['super_admin'],
                    'status' => $security_user['status'],
                    'is_student' => $security_user['is_student'],
                    'is_staff' => $security_user['is_staff'],
                    'is_guardian' => $security_user['is_guardian'],
                    'name' => $security_user['first_name'] . " " . $security_user['last_name'],
                    'date_of_birth' => date('Y-m-d', strtotime($security_user['date_of_birth'])),
                    'gender' => $security_user['Genders_name'],
                    'nationality_id' => $MainNationalities_id,
                    'nationality' => $MainNationalities_name,
                    'identity_type_id' => $MainIdentityTypes_id,
                    'identity_type' => $MainIdentityTypes_name,
                    'identity_number' => $identity_number,
                    'has_special_needs' => $has_special_needs,
                    'area_name' => $security_user['area_name'],
                    'area_code' => $security_user['area_code'],
                    'birth_area_name' => $security_user['birth_area_name'],
                    'birth_area_code' => $security_user['birth_area_code'],
                    'is_same_school' => $is_same_school,
                    'is_diff_school' => $is_diff_school,
                    'is_pending_withdraw' => $is_pending_withdraw,
                    'is_pending_transfer' => $is_pending_transfer,
                    'current_enrol_institution_id' => $institution_id,
                    'current_enrol_institution_name' => $institution_name,
                    'current_enrol_institution_code' => $institution_code,
                    'pending_withdraw_institution_code' => $pending_withdraw_institution_code,
                    'pending_withdraw_institution_name' => $pending_withdraw_institution_name,
                    'pending_transfer_prev_institution_code' => $pending_transfer_prev_institution_code,
                    'pending_transfer_prev_institution_name' => $pending_transfer_prev_institution_name,
                    'pending_transfer_institution_code' => $pending_transfer_institution_code,
                    'pending_transfer_institution_name' => $pending_transfer_institution_name,
                    'current_enrol_academic_period_id' => $academic_period_id,
                    'current_enrol_academic_period_year' => $academic_period_year,
                    'current_enrol_education_grade_id' => $education_grade_id,
                    'institution_name' => $institutionsTbl->institution_name??Null,
                    'institution_code' => $institutionsTbl->institution_code??Null,
                    'positions' => $positionArray??NULL,
                    'account_type' => $account_type,
                    'custom_data' => $CustomDataArray];

            }
            $userInternalSearch = ['data' => $user_internal_search_result, 'total' => $totalCount];

            return $userInternalSearch;

        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch User Data from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('User Data Not Found');
        }
    }



    public function getStudent($security_user_id)
    {
        try {
            $studentStatusCurrent = 1; //For student_status = CURRENT...
            $student = InstitutionStudent::join('institutions', 'institutions.id', '=', 'institution_students.institution_id')
                    ->where('institution_students.student_id', $security_user_id)
                    ->where('institution_students.student_status_id', $studentStatusCurrent)
                    ->select(
                        'institution_students.institution_id',
                        'institution_students.student_id',
                        'institution_students.student_status_id',
                        'institution_students.academic_period_id',
                        'institution_students.academic_period_year',
                        'institution_students.education_grade_id',
                        'institutions.name as institution_name',
                        'institutions.code as institution_code'
                    )
                    ->first();

                return $student;
        } catch (\Exception $e) {
            return false;
        }
    }



    public function getPendingTransfer($security_user_id)
    {
        try {
            $pendingTransfer = InstitutionStudentTransfers::join('institutions', 'institutions.id', '=', 'institution_student_transfers.institution_id')
                    ->join('institutions as prev_institutions', 'prev_institutions.id', '=', 'institution_student_transfers.previous_institution_id')
                    ->join('workflow_steps', 'workflow_steps.id', '=', 'institution_student_transfers.status_id')
                    ->where('institution_student_transfers.student_id', $security_user_id)
                    ->where('institution_student_transfers.status_id', '!=', 3)
                    ->select(
                        'institution_student_transfers.id',
                        'institution_student_transfers.institution_id',
                        'institution_student_transfers.previous_institution_id',
                        'institution_student_transfers.student_id',
                        'institution_student_transfers.academic_period_id',
                        'institutions.name as institution_name',
                        'institutions.code as institution_code',
                        'prev_institutions.name as previous_institution_name',
                        'prev_institutions.code as previous_institution_code',
                    )
                    ->first();
                return $pendingTransfer;
        } catch (\Exception $e) {
            return false;
        }
    }


    public function getPendingWithdraw($security_user_id)
    {
        try {
            $pendingWithdraw = InstitutionStudentWithdraw::join('institutions', 'institutions.id', '=', 'institution_student_withdraw.institution_id')
                    ->join('workflow_steps', 'workflow_steps.id', '=', 'institution_student_withdraw.status_id')
                    ->join('institutions as prev_institutions', 'prev_institutions.id', '=', 'institution_student_withdraw.previous_institution_id')
                    ->where('institution_student_withdraw.student_id', $security_user_id)
                    ->where('institution_student_withdraw.status_id', '!=', 3)
                    ->select(
                        'institution_student_withdraw.id',
                        'institution_student_withdraw.institution_id',
                        'institution_student_withdraw.previous_institution_id',
                        'institution_student_withdraw.student_id',
                        'institution_student_withdraw.academic_period_id',
                        'institutions.name as institution_name',
                        'institutions.code as institution_code',
                        'prev_institutions.name as previous_institution_name',
                        'prev_institutions.code as previous_institution_code',
                    )
                    ->first();

            return $pendingWithdraw;
        } catch (\Exception $e) {
            return false;
        }
    }


    public function getStudentCustomData($security_user_id)
    {
        try {
            $studentCustomData = StudentCustomFieldValues::leftJoin('student_custom_fields', 'student_custom_fields.id', '=', 'student_custom_field_values.student_custom_field_id')
                    ->leftJoin('student_custom_field_options', function ($q) {
                        $q->on('student_custom_field_options.student_custom_field_id', '=', 'student_custom_field_values.student_custom_field_id')
                            ->on('student_custom_field_options.id', '=', 'student_custom_field_values.number_value');
                    })
                    ->where('student_custom_field_values.student_id', $security_user_id)
                    ->select(
                        'student_custom_field_values.id',
                        'student_custom_field_values.student_custom_field_id',
                        'student_custom_fields.id as custom_id',
                        'student_custom_field_values.student_id',
                        'student_custom_field_values.text_value',
                        'student_custom_field_values.number_value',
                        'student_custom_field_values.decimal_value',
                        'student_custom_field_values.textarea_value',
                        'student_custom_field_values.date_value',
                        'student_custom_field_values.time_value',
                        'student_custom_field_options.name as option_value_text',
                        'student_custom_fields.name as name',
                        'student_custom_fields.field_type as field_type'
                    )
                    ->get()
                    ->toArray();

            $custom_field = array();
            $count = 0;
            if (!empty($studentCustomData)) {
                foreach ($studentCustomData as $val) {
                    $custom_field['custom_field'][$count]["id"] = (!empty($val['custom_id']) ? $val['custom_id'] : '');
                    $custom_field['custom_field'][$count]["name"] = (!empty($val['name']) ? $val['name'] : '');
                    $fieldTypes[$count] = (!empty($val['field_type']) ? $val['field_type'] : '');
                    $fieldType = $fieldTypes[$count];
                    if ($fieldType == 'TEXT') {
                        $custom_field['custom_field'][$count]["text_value"] = (!empty($val['text_value']) ? $val['text_value'] : '');
                    } else if ($fieldType == 'CHECKBOX') {
                        $custom_field['custom_field'][$count]["checkbox_value"] = (!empty($val['option_value_text']) ? $val['option_value_text'] : '');
                    } else if ($fieldType == 'NUMBER') {
                        $custom_field['custom_field'][$count]["number_value"] = (!empty($val['number_value']) ? $val['number_value'] : '');
                    } else if ($fieldType == 'DECIMAL') {
                        $custom_field['custom_field'][$count]["decimal_value"] = (!empty($val['decimal_value']) ? $val['decimal_value'] : '');
                    } else if ($fieldType == 'TEXTAREA') {
                        $custom_field['custom_field'][$count]["textarea_value"] = (!empty($val['textarea_value']) ? $val['textarea_value'] : '');
                    } else if ($fieldType == 'DROPDOWN') {
                        $custom_field['custom_field'][$count]["dropdown_value"] = (!empty($val['option_value_text']) ? $val['option_value_text'] : '');
                    } else if ($fieldType == 'DATE') {
                        $custom_field['custom_field'][$count]["date_value"] = date('Y-m-d', strtotime($val['date_value']));
                    } else if ($fieldType == 'TIME') {
                        $custom_field['custom_field'][$count]["time_value"] = date('h:i A', strtotime($val['time_value']));
                    } else if ($fieldType == 'COORDINATES') {
                        $custom_field['custom_field'][$count]["cordinate_value"] = (!empty($val['text_value']) ? $val['text_value'] : '');
                    }
                    $count++;
                }
            }
            
            return $custom_field;
        } catch (\Exception $e) {
            return false;
        }
    }


    public function getStaffCustomData($security_user_id)
    {
        try {
            $studentCustomData = StaffCustomFieldValues::leftJoin('staff_custom_fields', 'staff_custom_fields.id', '=', 'staff_custom_field_values.staff_custom_field_id')
                    ->leftJoin('staff_custom_field_options', function ($q) {
                        $q->on('staff_custom_field_options.staff_custom_field_id', '=', 'staff_custom_field_values.staff_custom_field_id')
                            ->on('staff_custom_field_options.id', '=', 'staff_custom_field_values.number_value');
                    })
                    ->where('staff_custom_field_values.student_id', $security_user_id)
                    ->select(
                        'staff_custom_field_values.id',
                        'staff_custom_field_values.staff_custom_field_id',
                        'staff_custom_fields.id as custom_id',
                        'staff_custom_field_values.staff_id',
                        'staff_custom_field_values.text_value',
                        'staff_custom_field_values.number_value',
                        'staff_custom_field_values.decimal_value',
                        'staff_custom_field_values.textarea_value',
                        'staff_custom_field_values.date_value',
                        'staff_custom_field_values.time_value',
                        'staff_custom_field_options.name as option_value_text',
                        'staff_custom_fields.name as name',
                        'staff_custom_fields.field_type as field_type'
                    )
                    ->get()
                    ->toArray();
            
            $custom_field = array();
            $count = 0;

            if (!empty($staffCustomData)) {
                foreach ($staffCustomData as $val) {
                    $custom_field['custom_field'][$count]["id"] = (!empty($val['custom_id']) ? $val['custom_id'] : '');
                    $custom_field['custom_field'][$count]["name"] = (!empty($val['name']) ? $val['name'] : '');
                    $fieldTypes[$count] = (!empty($val['field_type']) ? $val['field_type'] : '');
                    $fieldType = $fieldTypes[$count];
                    if ($fieldType == 'TEXT') {
                        $custom_field['custom_field'][$count]["text_value"] = (!empty($val['text_value']) ? $val['text_value'] : '');
                    } else if ($fieldType == 'CHECKBOX') {
                        $custom_field['custom_field'][$count]["checkbox_value"] = (!empty($val['option_value_text']) ? $val['option_value_text'] : '');
                    } else if ($fieldType == 'NUMBER') {
                        $custom_field['custom_field'][$count]["number_value"] = (!empty($val['number_value']) ? $val['number_value'] : '');
                    } else if ($fieldType == 'DECIMAL') {
                        $custom_field['custom_field'][$count]["decimal_value"] = (!empty($val['decimal_value']) ? $val['decimal_value'] : '');
                    } else if ($fieldType == 'TEXTAREA') {
                        $custom_field['custom_field'][$count]["textarea_value"] = (!empty($val['textarea_value']) ? $val['textarea_value'] : '');
                    } else if ($fieldType == 'DROPDOWN') {
                        $custom_field['custom_field'][$count]["dropdown_value"] = (!empty($val['option_value_text']) ? $val['option_value_text'] : '');
                    } else if ($fieldType == 'DATE') {
                        $custom_field['custom_field'][$count]["date_value"] = date('Y-m-d', strtotime($val['date_value']));
                    } else if ($fieldType == 'TIME') {
                        $custom_field['custom_field'][$count]["time_value"] = date('h:i A', strtotime($val['time_value']));
                    } else if ($fieldType == 'COORDINATES') {
                        $custom_field['custom_field'][$count]["cordinate_value"] = (!empty($val['text_value']) ? $val['text_value'] : '');
                    }
                    $count++;
                }
            }

            return $custom_field;
        } catch (\Exception $e) {
            return false;
        }
    }


    public function getRelationshipTypes($params)
    {
        try {
            $list = GuardianRelation::where('visible', 1)->orderBy('order', 'ASC')->get()->toArray();
            
            return $list;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Relationship Types from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Relationship Types Not Found');
        }
    }


    public function getStaffType($params)
    {
        try {
            $list = StaffTypes::where("visible", 1)->get()->toArray();
            $total = count($list);
            $resp['list'] = $list;
            $resp['total'] = $total;

            return $resp;
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Staff Types from DB',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse('Staff Types Not Found');
        }
    }

    //For POCOR-8104 End...


    //For POCOR-8194 Start...
    public function getStaffPositionGrades($params)
    {
        try {
            $result_array = [];
            if(isset($params['id'])){
                $id = $params['id'];

                $insPostionData = InstitutionPositions::where('id', $id)->first();
                $staff_position_title_id = $insPostionData->staff_position_title_id??0;
                $staffPositionTitleGrades = StaffPositionTitleGrade::where('staff_position_title_id', $staff_position_title_id)->get()->toArray();

                $id_arr = [];
                foreach($staffPositionTitleGrades as $key => $data){
                    $id_arr[$key] = $data['staff_position_grade_id'];
                }

                

                if(count($id_arr) > 0){
                    if($id_arr[0] == '-1'){
                        $staff_position_grades_result = StaffPositionGrades::select('id', 'name')->where('visible', 1)->get()->toArray();
                    } else {
                        $staff_position_grades_result = StaffPositionGrades::select('id', 'name')->where('visible', 1)->whereIn('id', $id_arr)->get()->toArray();
                    }

                    foreach ($staff_position_grades_result AS $result) {
                        $result_array[] = array("id" => $result['id'], "name" => $result['name']);
                    }
                }
                
            }

            return $result_array;
            
        } catch (\Exception $e) {
            Log::error(
                'Failed to fetch Staff position grades.',
                ['message'=> $e->getMessage(), 'trace' => $e->getTraceAsString()]
            );
            return $this->sendErrorResponse($e->getMessage());
        }
    }
    //For POCOR-8194 End...

}


        
